/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.lct.eq2.multicast;

import com.lct.eq2.data.DataHandler;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author waite
 */
public class RankingThread extends Thread {
    Connection conn = null;
    
    public static void main (String[] args) {
        RankingThread thread = new RankingThread();
        thread.start();
    }
    
    public RankingThread() {
        this.conn = this.openConnection();
    }
    
    @Override
    public void run() {
        long before = System.currentTimeMillis();
        System.out.println("RankingThread::run() - Generating rankings...");
        this.processWorldwideRankings();
        System.out.println("RankingThread::run() - Finished generating rankings. Took "
                + (System.currentTimeMillis() - before) + "ms.");
        this.closeConnection(conn);
    }
    
    private void processWorldwideRankings() {
//        String tmpSQL = "SELECT * from warct.character_data";
//        ResultSet tmpRS = this.executeSQL(tmpSQL);
//        DataHandler handler = new DataHandler();
//        try {
//            while (tmpRS.next()) {
//                String sql1 = "SELECT * from warct.attacks where character_id = " + tmpRS.getInt("character_id") + " limit 20";
//                ResultSet rs1 = this.executeSQL(sql1);
//                while (rs1.next()) {
//                    if (rs1.getString("att_attacker").equalsIgnoreCase(tmpRS.getString("character_name")) && !rs1.getString("att_name").equalsIgnoreCase("attack") && !rs1.getString("att_name").equalsIgnoreCase("axe toss")) {
//                        String attackName = rs1.getString("att_name");
//                        
//                        String career = handler.getCareerName(attackName);
//                        if (career != null && !career.equalsIgnoreCase("null")) {
//                            System.out.println(career);
//                            String sql2 = "UPDATE warct.character_stats set career_name='" + career + "' where character_id=" + tmpRS.getString("character_id");
//                            this.executeSQL(sql2);
//                            break;
//                        }
//                    }
//                }
//            }
//        } catch (SQLException ex) {
//            Logger.getLogger(RankingThread.class.getName()).log(Level.SEVERE, null, ex);
//        }
//        this.closeConnection(conn);
//        System.exit(0);
        
        this.executeSQL("TRUNCATE table warct.rankings");
        String sql = "SELECT character_id from warct.character_data";
        String updateSQL = "INSERT into warct.rankings values (?,?,?,?,?,?,?,?,?,?,?,?,?)";
        
        ResultSet rs = this.executeSQL(sql);
        try {
            while (rs.next()) {
                int charID = rs.getInt("character_id");
                Stats stats = this.getCharacterStats(charID);
                double incomingAttacks = (double)stats.getTotalIncomingAttacks();
                double totalSecondsFighting = (double)stats.getTotalSecondsFighting();
                double outgoingAttacks = (double)stats.getTotalOutgoingAttacks();
                
                double avoidRate = (double)stats.getTotalIncomingAvoids() / incomingAttacks;
                if (incomingAttacks == 0) {
                    avoidRate = 0;
                }
                double dps = (double)stats.getTotalOutgoingDamage() / totalSecondsFighting;
                if (totalSecondsFighting == 0) {
                    dps = 0;
                }
                double critRate = (double)stats.getTotalOutgoingCrits() / outgoingAttacks;
                if (outgoingAttacks == 0) {
                    critRate = 0;
                }
                
                Object[] data = {stats.getCharID(), stats.getTotalOutgoingDamage(), 
                    stats.getTotalSecondsFighting(), stats.getTotalIncomingDamage(),
                    avoidRate, stats.getHighestOutgoingMeleeHit(), stats.getHighestOutgoingNonMeleeHit(),
                    stats.getHighestOutgoingHeal(), critRate, dps, stats.getRank(), stats.getRenownRank(),
                    stats.getCareer()};
                this.executeUpdatePreparedStatement(updateSQL, data);
                
            }
        } catch (SQLException ex) {
            Logger.getLogger(RankingThread.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    public Connection openConnection() {
        Connection rval = null;

        String username;
        String password;
        String url;

        Properties props = new Properties();
        try {
            props.load(new FileInputStream(new File("database.properties")));
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

        username = props.getProperty("username");
        password = props.getProperty("password");
        url = props.getProperty("url");

        String driverName = "com.mysql.jdbc.Driver";
        try {
            Class.forName(driverName);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        try {
            rval = DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rval;
    }

    public void closeConnection(Connection conn) {
        try {
            conn.close();
        } catch (NullPointerException e) {
        // connection not open
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public Stats getCharacterStats(int characterID) {
        Stats rval = new Stats();
        String sql = "SELECT * from character_stats where character_id=" + characterID;
        ResultSet rs = this.executeSQL(sql);
        try {
            while (rs.next()) {
                rval.setCharID(characterID);
                rval.setTotalOutgoingAttacks(rs.getInt("total_outgoing_attacks"));
                rval.setTotalIncomingAttacks(rs.getInt("total_incoming_attacks"));
                rval.setTotalIncomingAvoids(rs.getInt("total_incoming_avoids"));
                rval.setTotalOutgoingAvoids(rs.getInt("total_outgoing_avoids"));
                rval.setTotalOutgoingDamage(rs.getInt("total_outgoing_damage"));
                rval.setTotalDeaths(rs.getInt("total_deaths"));
                rval.setTotalIncomingBlocks(rs.getInt("total_incoming_blocks"));
                rval.setTotalIncomingDisrupts(rs.getInt("total_incoming_disrupts"));
                rval.setTotalIncomingDodges(rs.getInt("total_incoming_dodges"));
                rval.setTotalIncomingParries(rs.getInt("total_incoming_parries"));
                rval.setTotalOutgoingBlocks(rs.getInt("total_outgoing_blocks"));
                rval.setTotalOutgoingDisrupts(rs.getInt("total_outgoing_disrupts"));
                rval.setTotalOutgoingDodges(rs.getInt("total_outgoing_dodges"));
                rval.setTotalOutgoingParries(rs.getInt("total_outgoing_parries"));
                rval.setTotalIncomingDamage(rs.getInt("total_incoming_damage"));
                rval.setRank(rs.getInt("total_rank"));
                rval.setRenownRank(rs.getInt("total_renown_rank"));
                rval.setTotalIncomingCrits(rs.getInt("total_incoming_crits"));
                rval.setTotalOutgoingCrits(rs.getInt("total_outgoing_crits"));
                rval.setTotalSecondsFighting(rs.getLong("total_seconds_fighting"));
                
                rval.setHighestIncomingMeleeHit(rs.getLong("highest_incoming_melee"));
                rval.setLowestIncomingMeleeHit(rs.getLong("lowest_incoming_melee"));
                rval.setHighestIncomingNonMeleeHit(rs.getLong("highest_incoming_non_melee"));
                rval.setLowestIncomingNonMeleeHit(rs.getLong("lowest_incoming_non_melee"));
                rval.setHighestOutgoingMeleeHit(rs.getLong("highest_outgoing_melee"));
                rval.setLowestOutgoingMeleeHit(rs.getLong("lowest_outgoing_melee"));
                rval.setHighestOutgoingNonMeleeHit(rs.getLong("highest_outgoing_non_melee"));
                rval.setLowestOutgoingNonMeleeHit(rs.getLong("lowest_outgoing_non_melee"));
                rval.setHighestIncomingHeal(rs.getLong("highest_incoming_heal"));
                rval.setHighestOutgoingHeal(rs.getLong("highest_outgoing_heal"));
                rval.setCareer(rs.getString("career_name"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseHelperThread.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rval;
    }
    
    public int getCharacterID(String charName, String serverName) {
        int rval = -1;
        try {
            int serverID = this.getServerID(serverName);
            String charIDSql = "SELECT character_id " + "FROM character_data where character_name='" + charName + "' " +
                    "AND server_id=" + serverID;
            //System.out.println(charIDSql);

            ResultSet rs = this.executeSQL(charIDSql);

            if (rs.next()) {
                rval = rs.getInt("character_id");
            } else {
                String insertSQL = "INSERT into character_data (character_name, server_id) " +
                        "values ('" + charName + "'," + serverID + ")";
                rs = this.executeSQL(insertSQL);

                if (rs.next()) {
                    rval = rs.getInt(1);
                    insertSQL = "INSERT into character_stats (character_id) " +
                        "values (" + rval + ")";
                    this.executeSQL(insertSQL);
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseHelperThread.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rval;
    }

    public int getServerID(String serverName) {
        int rval = -1;
        try {
            String serverIDSql = "SELECT server_id " + "FROM servers where server_name='" + serverName + "'";

            ResultSet rs = this.executeSQL(serverIDSql);

            if (rs.next()) {
                rval = rs.getInt("server_id");
            } else {
                String insertSQL = "INSERT into servers (server_name) values ('" + serverName + "')";
                rs = this.executeSQL(insertSQL);

                if (rs.next()) {
                    rval = rs.getInt(1);
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseHelperThread.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rval;
    }

    public ResultSet executePreparedStatement(String sql, ArrayList data) {
        ResultSet rval = null;
        try {
//            String[] columnNames = {"att_damage", "att_name", "att_type",
//            "att_aer", "att_avoided", "att_avoid_reason", "att_is_crit", "character_id", 
//            "att_target", "att_timestamp", "ref_encounter_id"};
            //PreparedStatement stmt = this.conn.prepareStatement(sql);
            PreparedStatement stmt = this.conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
            int count = 0;
            for (Object object : data) {
                Object[] params = (Object[])object;
                for (int x = 0; x < params.length; x++) {
                    Object param = params[x];
                    stmt.setObject(++count, param);
                }
            }
            
            stmt.executeUpdate();
            rval = stmt.getGeneratedKeys();
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseHelperThread.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rval;
    }
    
    public void executeUpdatePreparedStatement(String sql, Object[] data) {
        ResultSet rval = null;
        try {
//            String[] columnNames = {"att_damage", "att_name", "att_type",
//            "att_attacker", "att_avoided", "att_avoid_reason", "att_is_crit", "character_id", 
//            "att_target", "att_timestamp", "ref_encounter_id"};
            //PreparedStatement stmt = this.conn.prepareStatement(sql);
            PreparedStatement stmt = this.conn.prepareStatement(sql);
            for (int x = 0; x < data.length; x++) {
                Object object = data[x];
                stmt.setObject(x + 1, object);
            }
            
            stmt.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseHelperThread.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    public ResultSet executePreparedStatement(String sql, Object[] data) {
        ResultSet rval = null;
        try {
//            String[] columnNames = {"att_damage", "att_name", "att_type",
//            "att_attacker", "att_avoided", "att_avoid_reason", "att_is_crit", "character_id", 
//            "att_target", "att_timestamp", "ref_encounter_id"};
            //PreparedStatement stmt = this.conn.prepareStatement(sql);
            PreparedStatement stmt = this.conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
            for (int x = 0; x < data.length; x++) {
                Object object = data[x];
                stmt.setObject(x + 1, object);
            }
            
            stmt.executeUpdate();
            rval = stmt.getGeneratedKeys();
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseHelperThread.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rval;
    }

    public ResultSet executeSQL(String sql) {
        ResultSet rval = null;
        try {
            Statement stmt = this.conn.createStatement();
            if (sql.contains("SELECT")) {
                rval = stmt.executeQuery(sql);
            } else if (sql.contains("INSERT") || sql.contains("UPDATE")) {
                stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
                rval = stmt.getGeneratedKeys();
            } else if (sql.contains("DELETE") || sql.contains("TRUNCATE")) {
                stmt.execute(sql);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseHelperThread.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rval;
    }
    
}
